﻿using FreeOPC.Config;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;

namespace FreeOPC.Server.sqlite
{
    public static class SqliteHelper
    {
        private static string serviceExePath = Assembly.GetExecutingAssembly().Location.Replace("FreeOPC.Server.dll","");


        //连接字符串，指定了文件名
        private static string connStr = "data source="+ serviceExePath + "opcserver.sqlite"; //数据库文件名字：db.sqlite

        //在这里创建所有表,可以反复执行,程序启动后调用
        public static void CreateDB()
        {
            //建表
            var opctableName = "CREATE TABLE  opcserver (Id INTEGER PRIMARY KEY,      DAProgId TEXT,      AEProgId TEXT,      OPCServerIP TEXT,      SubsystemTypeId INTEGER  )";
            var opctagitem = "CREATE TABLE  opctagitem (Id INTEGER PRIMARY KEY,      OPCIP TEXT,      TagItem TEXT )";
            SqliteHelper.ExecuteNonQuery(opctableName);
        }

        //执行命令的方法：insert,update,delete
        //params：可变参数，目的是省略了手动构造数组的过程，直接指定对象，编译器会帮助我们构造数组，并将对象加入数组中，传递过来
        public static int ExecuteNonQuery(string sql, params SQLiteParameter[] ps)
        {
            //创建连接对象
            using (SQLiteConnection conn = new SQLiteConnection(connStr))
            {
                //创建命令对象
                SQLiteCommand cmd = new SQLiteCommand(sql, conn);
                //添加参数
                cmd.Parameters.AddRange(ps);
                //打开连接
                conn.Open();
                //执行命令，并返回受影响的行数
                return cmd.ExecuteNonQuery();
            }
        }

        /// <summary>
        /// 插入OPC SERVER
        /// </summary>
        /// <param name="DAProgIdValue"></param>
        /// <param name="AEProgIdValue"></param>
        /// <param name="OPCServerIPValue"></param>
        /// <param name="SubsystemTypeId"></param>
        /// <returns></returns>
        public static bool InsertOPC(string DAProgIdValue, string AEProgIdValue, string OPCServerIPValue, int SubsystemTypeId)
        {
            try
            {
                string sql = string.Format("INSERT INTO opcserver (DAProgId, AEProgId, OPCServerIP, SubsystemTypeId) VALUES ('{0}', '{1}', '{2}', {3})", DAProgIdValue, AEProgIdValue, OPCServerIPValue, SubsystemTypeId);
                using (SQLiteConnection conn = new SQLiteConnection(connStr))
                {
                    SQLiteCommand cmd = new SQLiteCommand(sql, conn);
                    //cmd.Parameters.AddWithValue("@DAProgId", DAProgIdValue);
                    //cmd.Parameters.AddWithValue("@AEProgId", AEProgIdValue);
                    //cmd.Parameters.AddWithValue("@OPCServerIP", OPCServerIPValue);
                    //cmd.Parameters.AddWithValue("@SubsystemTypeId", SubsystemTypeId);

                    conn.Open();
                    int rowsAffected = cmd.ExecuteNonQuery();

                    // 插入成功后的处理逻辑
                    if (rowsAffected > 0)
                    {
                        return true;
                    }
                    return false;
                }
            }
            catch (Exception ex)
            {
                return false;
            }
        }

        /// <summary>
        /// 删除OPC SERVER
        /// </summary>
        /// <param name="OPCServerIP"></param>
        /// <returns></returns>
        public static bool DeleteOPC(string OPCServerIP)
        {
            string sql = string.Format("DELETE FROM opcserver WHERE OPCServerIP = '{0}'", OPCServerIP);

            using (SQLiteConnection conn = new SQLiteConnection(connStr))
            {
                conn.Open();
                using (SQLiteCommand cmd = new SQLiteCommand(sql, conn))
                {
                    //cmd.Parameters.AddWithValue("@OPCServerIP", OPCServerIP);

                    int rowsAffected = cmd.ExecuteNonQuery();

                    // 删除成功后的处理逻辑
                    if (rowsAffected > 0)
                    {
                        return true;
                    }
                    return false;
                }
            }

        }

        /// <summary>
        /// 删除OPC SERVER
        /// </summary>
        /// <param name="OPCServerIP"></param>
        /// <returns></returns>
        public static bool DeleteALLOPC()
        {
            string sql = string.Format("DELETE FROM opcserver");

            using (SQLiteConnection conn = new SQLiteConnection(connStr))
            {
                conn.Open();
                using (SQLiteCommand cmd = new SQLiteCommand(sql, conn))
                {
                    //cmd.Parameters.AddWithValue("@OPCServerIP", OPCServerIP);

                    int rowsAffected = cmd.ExecuteNonQuery();

                    // 删除成功后的处理逻辑
                    if (rowsAffected > 0)
                    {
                        return true;
                    }
                    return false;
                }
            }

        }

        /// <summary>
        /// 删除OPC SERVER
        /// </summary>
        /// <param name="OPCServerIP"></param>
        /// <returns></returns>
        public static bool DeleteALLItem()
        {
            string sql = string.Format("DELETE FROM opctagitem");

            using (SQLiteConnection conn = new SQLiteConnection(connStr))
            {
                conn.Open();
                using (SQLiteCommand cmd = new SQLiteCommand(sql, conn))
                {
                    //cmd.Parameters.AddWithValue("@OPCServerIP", OPCServerIP);

                    int rowsAffected = cmd.ExecuteNonQuery();

                    // 删除成功后的处理逻辑
                    if (rowsAffected > 0)
                    {
                        return true;
                    }
                    return false;
                }
            }

        }


        /// <summary>
        /// 删除OPC SERVER
        /// </summary>
        /// <param name="OPCServerIP"></param>
        /// <returns></returns>
        public static bool DeleteItem(string TagItem,string OPCIP, string GroupName)
        {
            string sql = string.Format("DELETE FROM opctagitem WHERE OPCIP='{0}' and TagItem='{1}' and GroupName='{2}'", OPCIP, TagItem, GroupName);

            using (SQLiteConnection conn = new SQLiteConnection(connStr))
            {
                conn.Open();
                using (SQLiteCommand cmd = new SQLiteCommand(sql, conn))
                {
                    //cmd.Parameters.AddWithValue("@TagItem", TagItem);

                    int rowsAffected = cmd.ExecuteNonQuery();

                    // 删除成功后的处理逻辑
                    if (rowsAffected > 0)
                    {
                        return true;
                    }
                    return false;
                }
            }

        }

        /// <summary>
        /// 插入ITEM
        /// </summary>
        /// <param name="OPCIP"></param>
        /// <param name="TagItem"></param>
        /// <returns></returns>
        public static bool InsertItem(string OPCIP, string TagItem,string GroupName)
        {
            try
            {
                string sql = string.Format("INSERT INTO opctagitem (OPCIP, TagItem,GroupName) VALUES ('{0}', '{1}','{2}')", OPCIP, TagItem, GroupName);
                string selectsql = string.Format("select Id from opctagitem where OPCIP='{0}' and TagItem='{1}' and GroupName='{2}'", OPCIP, TagItem, GroupName);
                var items = GetDataTable(selectsql);
                if (items.Rows.Count == 0)
                {
                    using (SQLiteConnection conn = new SQLiteConnection(connStr))
                    {
                        SQLiteCommand cmd = new SQLiteCommand(sql, conn);
                        //cmd.Parameters.AddWithValue("@DAProgId", DAProgIdValue);
                        //cmd.Parameters.AddWithValue("@AEProgId", AEProgIdValue);
                        //cmd.Parameters.AddWithValue("@OPCServerIP", OPCServerIPValue);
                        //cmd.Parameters.AddWithValue("@SubsystemTypeId", SubsystemTypeId);

                        conn.Open();
                        int rowsAffected = cmd.ExecuteNonQuery();

                        // 插入成功后的处理逻辑
                        if (rowsAffected > 0)
                        {
                            return true;
                        }
                        return false;
                    }
                }
                else
                {
                    return true;
                }
            }
            catch (Exception ex)
            {
                return false;
            }
        }



        //获取结果集
        public static DataTable GetDataTable(string sql, params SQLiteParameter[] ps)
        {
            SerilogServer.WriteLog("OPCClientWindows", new string[] { connStr });
            using (SQLiteConnection conn = new SQLiteConnection(connStr))
            {
                //构造适配器对象
                SQLiteDataAdapter adapter = new SQLiteDataAdapter(sql, conn);
                //构造数据表，用于接收查询结果
                DataTable dt = new DataTable();
                //添加参数
                adapter.SelectCommand.Parameters.AddRange(ps);
                //执行结果
                adapter.Fill(dt);
                //返回结果集
                return dt;
            }
        }

        //获取首行首列值的方法
        public static object ExecuteScalar(string sql, params SQLiteParameter[] ps)
        {
            using (SQLiteConnection conn = new SQLiteConnection(connStr))
            {
                SQLiteCommand cmd = new SQLiteCommand(sql, conn);
                cmd.Parameters.AddRange(ps);

                conn.Open();
                //执行命令，获取查询结果中的首行首列的值，返回
                return cmd.ExecuteScalar();
            }
        }
    }

}
